﻿using DXApplicationTangche.UC.报表.service;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Windows.Forms;

namespace DXApplicationTangche.Utils
{
    public class ExcelTemplateUtil
    {
        /// <summary>
        /// 根据模板导出多个sheet页数据
        /// </summary>
        /// <param name="excelDTOs"></param>
        /// <param name="fileName"></param>
        /// <param name="filePath"></param>
        public static void ToExcelTemplate(List<ExcelTemplateDTO> excelDTOs, string fileName, string filePath)
        {
            //  需要添加 Microsoft.Office.Interop.Excel引用
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            if (app == null)
            {
                HttpContext.Current.Response.Write("服务器上缺少Excel组件，需要安装Office软件。");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(MapPath(filePath)); //加载模板
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;

            for (int a = 0; a < excelDTOs.Count; a++)
            {
                ExcelTemplateDTO excelDTO = excelDTOs[a];
                //  指定sheet页
                Microsoft.Office.Interop.Excel._Worksheet worksheet =
                    (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(excelDTO.tableItem); //第几个工作薄

                if (worksheet == null)
                    return;  //工作薄中没有工作表.
                             //1、获取数据。
                             //DataSet dt = myDB.Select(sql, null);  //--------------------------------------------------根据实际需要修改--------------！！！！！
                int rowCount = excelDTO.dataTable.Rows.Count;//总行数
                int col = excelDTO.dataTable.Columns.Count;//总列数
                if (rowCount < 1)
                    return; //没有数据，不需要导出。
                            //2、写入数据，Excel索引从1开始。
                for (int i = 1; i <= rowCount; i++)
                {
                    int row_ = excelDTO.lineNumber + i;  //Excel模板上表头和标题行占了2行,根据实际模板需要修改;
                    int dt_row = i - 1; //dataTable的行是从0开始的。
                    int colNumber = excelDTO.colNumber + 1;
                    //循环每一列
                    for (int b = 0; b < col; b++)
                    {
                        try
                        {
                            worksheet.Cells[row_, b + colNumber] = excelDTO.dataTable.Rows[i - 1][b];
                        }
                        catch (Exception)
                        {
                            worksheet.Cells[row_, b + colNumber] = 0;
                        }
                    }
                }
                //调整Excel的样式。
                //Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + 2, 8]);
                //rg.Borders.LineStyle = 1; //单元格加边框。
                worksheet.Columns.AutoFit(); //自动调整列宽。
            }
            string savaPath = fileName;
            //如果已经有了就删除原来的
            if (File.Exists(Path.GetFullPath(fileName)))
            {
                File.Delete(Path.GetFullPath(fileName));
            }
            workbook.SaveAs(Path.GetFullPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            ////4、按顺序释放资源。
            //NAR(worksheet);
            NAR(sheets);
            NAR(workbook);
            NAR(workbooks);
            ////自动下载
            app.Quit();
            NAR(app);
        }
        /// <summary>
        /// 导数据到Excel模板
        /// </summary>
        /// <param name="tab">要输出内容的Table</param>
        /// <param name="fileName">生成后的路径带文件名</param>
        /// <param name="filePath">模板路径带文件名</param>
        /// <param name="lineNumber">表头占了几行</param>
        /// <param name="colNumber">表头占了几列</param>
        /// <param name="tableItem">Excel的第几个工作薄</param>
        public static void ToExcelTemplate(DataTable tab, string fileName, string filePath, int lineNumber, int colNumber, int tableItem)
        {
            //需要添加 Microsoft.Office.Interop.Excel引用
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            if (app == null)
            {
                HttpContext.Current.Response.Write("服务器上缺少Excel组件，需要安装Office软件。");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(MapPath(filePath)); //加载模板
            //Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(System.AppDomain.CurrentDomain.BaseDirectory + filePath); //加载模板
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
            Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(tableItem); //第几个工作薄。
            if (worksheet == null)
                return;  //工作薄中没有工作表.
                         //1、获取数据。
                         //DataSet dt = myDB.Select(sql, null);  //--------------------------------------------------根据实际需要修改--------------！！！！！
            int rowCount = tab.Rows.Count;//总行数
            int col = tab.Columns.Count;//总列数
            if (rowCount < 1)
                return; //没有数据，不需要导出。
                        //2、写入数据，Excel索引从1开始。
            for (int i = 1; i <= rowCount; i++)
            {
                int row_ = lineNumber + i;  //Excel模板上表头和标题行占了2行,根据实际模板需要修改;
                int dt_row = i - 1; //dataTable的行是从0开始的。
                colNumber = colNumber + 1;
                //循环每一列
                for (int b = 0; b < col; b++)
                {
                    worksheet.Cells[row_, b + colNumber] = tab.Rows[i - 1][b];
                }
            }
            //调整Excel的样式。
            //Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + 2, 8]);
            //rg.Borders.LineStyle = 1; //单元格加边框。
            worksheet.Columns.AutoFit(); //自动调整列宽。
                                         //3、保存生成的Excel文件。
                                         //Missing 在System.Reflection命名空间下。
            string savaPath = fileName;
            //如果已经有了就删除原来的
            if (File.Exists(Path.GetFullPath(fileName)))
            {
                File.Delete(Path.GetFullPath(fileName));
            }
            workbook.SaveAs(Path.GetFullPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);



            //4、按顺序释放资源。
            NAR(worksheet);
            NAR(sheets);
            NAR(workbook);
            NAR(workbooks);
            //自动下载
            app.Quit();
            NAR(app);
        }

        private static void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch
            { }
            finally
            {
                o = null;
            }
        }
        public static string MapPath(string strPath)
        {
            if (HttpContext.Current != null)
            {
                return HttpContext.Current.Server.MapPath(strPath);
            }
            else //非web程序引用
            {
                strPath = strPath.Replace("/", "\\");
                if (strPath.StartsWith("\\"))
                {
                    //strPath = strPath.Substring(strPath.IndexOf('\\', 1)).TrimStart('\\');
                    strPath = strPath.TrimStart('\\');
                }
                return System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, strPath);
            }
        }
    }
    public class ExcelTemplateDTO
    {
        public DataTable dataTable { get; set; }    // 数据
        public int lineNumber { get; set; } //  表头占了几行
        public int colNumber { get; set; } //  表头占了几列
        public int tableItem { get; set; }  //  哪个sheet页
        
        /// <param name="dataTable">数据</param>
        /// <param name="lineNumber">表头占了几行</param>
        /// <param name="colNumber">表头占了几列</param>
        /// <param name="tableItem">哪个sheet页</param>
        public ExcelTemplateDTO(DataTable dataTable, int lineNumber, int colNumber, int tableItem)
        {
            this.dataTable = dataTable;
            this.lineNumber = lineNumber;
            this.colNumber = colNumber;
            this.tableItem = tableItem;
        }
    }
}
